/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package javalee.dao;


import javalee.maps.Venda;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author DuDz
 */
public class VendaDAO extends DAO {

    private Statement stmt = null;
    
    @Override
    public boolean cadastrar(Object objeto) {
            try{
            Venda p = (Venda) objeto;
            String SQL = "insert into venda (id_cliente,data, data_entrega, valor) values ("+p.getCliente().getId()+",'"+p.getData()+"','"+p.getDataEntrega()+"', "+p.getValor()+")";
            stmt = conexaoBanco.createStatement();
            stmt.executeUpdate(SQL);
            stmt.close();
            //"insert into cliente (nome) values ('?')"
            return (true);
        } 
           catch (SQLException ex) {
             System.out.println("Problema com o SQL");
            // ex.printStackTrace();
             return (false);
       }
    }

    @Override
    public boolean alterar(Object objeto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public boolean excluir(Object objeto) {
        try{
            Venda p = (Venda) objeto;
            //Deleta os itens da venda
            String SQL = "delete from itens_venda where id_venda = "+p.getId();
            stmt = conexaoBanco.createStatement();
            stmt.executeUpdate(SQL);
            //Deleta a venda
            SQL = "delete from venda where id = "+p.getId();
            stmt = conexaoBanco.createStatement();
            stmt.executeUpdate(SQL);
            stmt.close();
            //"insert into cliente (nome) values ('?')"
            return (true);
        } 
           catch (SQLException ex) {
             System.out.println("Problema com o SQL");
            // ex.printStackTrace();
             return (false);
       }
    }
    @Override
    public ArrayList consultar(Object objeto) {
        
       ArrayList<Venda> lista = new ArrayList<Venda>();
        //List<Person> personlist = new List<Person>();
       
       clienteDAO cliDAO = new clienteDAO();
        try {
            stmt = conexaoBanco.createStatement();
            String query = "SELECT v.id as id,v.id_cliente as id_cliente,date_format(v.data, '%d/%m/%Y') as data, "
                    + "date_format(v.data_entrega, '%d/%m/%Y') as data_entrega, v.valor as valor  from venda v INNER JOIN cliente c on v.id_cliente = c.id";
            if(!objeto.toString().equals("")){
                query = query.concat(" WHERE c.nome LIKE '"+objeto.toString()+"%'");
            }
            query += " ORDER BY v.id LIMIT 100";
            ResultSet srs = stmt.executeQuery(query);
            while (srs.next()) {
                Venda v = new Venda();
                v.setCliente(cliDAO.consultarID(srs.getInt("id_cliente")));
                v.setId(srs.getInt("id"));
                v.setData(srs.getString("data"));
                v.setDataEntrega(srs.getString("data_entrega"));
                v.setValor(srs.getInt("valor"));
                
                lista.add(v);
            }
           
            stmt.close();
        }
        
           catch (SQLException ex) {
             System.out.println("Problema com o SQL");
             //ex.printStackTrace();
             return (null);
       }
 return lista;
}
    
    public Venda consultarPorId(Integer obj) {
        Venda v = new Venda();
        clienteDAO c = new clienteDAO();
         try {
            stmt = conexaoBanco.createStatement();
            String query = "SELECT id,id_cliente,date_format(v.data, '%d/%m/%Y') as data,"
                    + "date_format(v.data_entrega, '%d/%m/%Y') as data_entrega, v.valor as valor"
                    + " WHERE id = "+obj.toString()+" LIMIT 1";
            
            ResultSet srs = stmt.executeQuery(query);
            
            
            while (srs.next()) {
                v.setId(srs.getInt("id"));
                v.setCliente(c.consultarID(srs.getInt("id_cliente")));
                v.setData(srs.getString("data"));
                v.setDataEntrega(srs.getString("data_entrega"));
                v.setValor(srs.getInt("valor"));
                }
            
     }
     catch(Exception e){}
     return (v);    
    }
    
     public int getUltimoID(){
         int id = 0;
         try{
        stmt = conexaoBanco.createStatement();
        String query = "SELECT last_insert_id() as id";
        ResultSet srs = stmt.executeQuery(query);
         while (srs.next()) {
            id = srs.getInt("id");
         }
         }catch(SQLException e){
            System.out.println("Problema com o SQL");
            return 0;
         }
         return id;
    }    
}
